Introduction to SQL Lecture Notes · Module 3 SQL Basics

SQL is a declarative language: you specify what result you want, and the DBMS decides how to compute it efficiently. This design is crucial because the DBMS can change execution strategies automatically as data sizes, indexes, and statistics change.

• You describe the desired result set.
• The DBMS chooses access paths (index scan vs sequential scan).
• The DBMS chooses join order and join algorithms (nested-loop, hash join, sort-merge).
• The DBMS may use parallelism for speedups on multi-core systems.

• DDL (Data Definition Language) — define structure: CREATE, ALTER, DROP, CREATE INDEX
• DML (Data Manipulation Language) — query & modify data: SELECT, INSERT, UPDATE, DELETE, MERGE
• TCL (Transaction Control Language) — transaction boundaries: COMMIT, ROLLBACK, SAVEPOINT
• DCL (Data Control Language) — permissions: GRANT, REVOKE

• Analytics: window functions, CTEs, recursive queries.
• Semi-structured: JSON types + JSON path/query functions (vendor-dependent).
• Scale: partitioning, parallel execution, columnar storage/engines in some systems.
• Beyond relational: graph querying is entering the standard (SQL/PGQ), but adoption is uneven.

Although SQL is standardized (ISO/IEC 9075), no DBMS implements the entire standard. Instead, systems share a large common core and differ in optional features and extensions.

• PostgreSQL: strong standards compliance; powerful extensions; excellent JSON support.
• MySQL / MariaDB: widely used in web stacks; feature sets differ by version and engine.
• SQL Server: rich tooling; strong analytics and enterprise integration.
• Oracle: enterprise features; extensive optimizer and scalability support.
• SQLite: lightweight embedded DB; great for mobile and local applications.

SQL provides a rich set of data types to represent numbers, text, dates/times, logical values, and binary objects. While the SQL standard defines broad categories, exact support and naming may vary across database vendors.

Numeric data types

Character and binary data types

Date and time data types

Boolean and special-purpose types

Not all SQL data types are supported uniformly across database vendors. Names, limits, and behavior may differ.

The data types listed here are the most commonly used. Real systems often provide additional vendor-specific types (e.g., MONEY in SQL Server, spatial types, UUIDs).

Each DBMS enforces its own maximum size limits for strings, binary objects, and numeric precision. Always consult vendor documentation when designing schemas.

Most SQL work happens in a client–server model: a client sends SQL; the server parses, optimizes, executes, and returns rows.

This separation explains why credentials, network ports, and server status matter: your SQL is executed by the server, not your laptop.

MySQL is architected with a SQL layer on top of storage engines. The default engine is InnoDB, which supports transactions (ACID), row-level locking, crash recovery, and foreign keys.

• Core relational SQL: joins, grouping/aggregation, constraints, indexes.
• Transactions (InnoDB): COMMIT/ROLLBACK, isolation concepts.
• Modern SQL features (MySQL 8+): common table expressions (CTEs) and window functions.
• JSON support: MySQL supports a native JSON type (introduced in 5.7 and expanded in 8.x).
• Security basics: authentication, privileges (GRANT/REVOKE), TLS support (configuration-dependent).

“LTS (Long-Term Support)” is what many production systems prefer (stable, long support). Innovation releases deliver new features faster. For this course, we focus on portable SQL concepts; version details matter mainly for feature availability.

Numeric

String & Binary

Date & Time

Other

Refer to the official website for details. The following steps are only for the installation of MySQL on macOS.

Step 1: Download MySQL Server (Community Edition) for macOS.

Step 2: Click the downloaded .dmg file to open the MySQL Server installer package. During the installation, it will display a “Configure MySQL Server” dialog; set the root user password and click Finish to complete the installation.

Step 3: Enable the launched service: open macOS System Preferences, select the MySQL preference panel, and click Start MySQL Server.

Step 4: Connect to the MySQL server by running mysql -u root -p. You can disconnect by entering exit. Note that the MySQL server is still running in the background.

(base) wtong@wt-m2 ~ % mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> EXIT
Bye
(base) wtong@wt-m2 ~ %

Common problem 1: How to fix “mysql command not found”

Common problem 2: If you forget the root password, you can use the following steps on macOS.

1. Make sure you have stopped MySQL first.

   Go to: 'System Preferences' >> 'MySQL' and stop MySQL

   OR,

   sudo /usr/local/mysql/support-files/mysql.server start
   sudo /usr/local/mysql/support-files/mysql.server stop
   sudo /usr/local/mysql/support-files/mysql.server status

2. Run the server in safe mode with privilege bypass: sudo mysqld_safe --skip-grant-tables
3. In a new window connect to the database, set a new password, flush permissions, and quit:

   mysql -u root
   UPDATE mysql.user SET authentication_string=null WHERE User='root';
   FLUSH PRIVILEGES;
   exit;

   Then:

   mysql -u root
   ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

Cheat Sheet:

• MySQL Cheat Sheet
• MySQL Cheat Sheet from learnSQL.com

Practice Questions:

• SQL Practice
• SQL Exercises
• SQL Exercises, Practice, Solution
• HackerRank: SQL

Think “client → server”. The mysql program is a client; mysqld is the server daemon. You must know host, port (default 3306), username, and password.

(base) wtong@wt-m2 ~ % mysql -h host -u user -p
Enter password: *******

host and user represent the host name where your MySQL server is running and the user name of your MySQL account. If you are logging in on the same machine that MySQL is running on, you can omit the host:

(base) wtong@wt-m2 ~ % mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> QUIT
Bye
(base) wtong@wt-m2 ~ %

mysql> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 8.0.32    | 2023-09-07   |
+-----------+--------------+
1 row in set (0.00 sec)

mysql>

• A query normally consists of an SQL statement followed by a semicolon. (There are exceptions where a semicolon may be omitted. QUIT is one of them.)

• When you issue a query, mysql sends it to the server for execution and displays the results, then prints another mysql> prompt.

• Output is shown in tabular form (rows and columns). Column labels usually come from selected columns (or the expression text if you selected an expression).

• The client shows the number of returned rows and the elapsed time. This is wall-clock time and depends on load and latency.

Keywords may be entered in any letter case. The following are equivalent:

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| WT                 |
+--------------------+
6 rows in set (0.00 sec)

mysql> CREATE DATABASE helloworld;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES
    -> ;
+--------------------+
| Database           |
+--------------------+
| helloworld         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| WT                 |
+--------------------+
7 rows in set (0.00 sec)

mysql> DROP DATABASE helloworld;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| WT                 |
+--------------------+
6 rows in set (0.01 sec)

mysql> CREATE DATABASE helloworld;
Query OK, 1 row affected (0.00 sec)

mysql> USE helloworld
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

/* USE, like QUIT, does not require a semicolon.
   (You can terminate such statements with a semicolon if you like; it does no harm.)
*/

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    ->        species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+----------------------+
| Tables_in_helloworld |
+----------------------+
| pet                  |
+----------------------+
1 row in set (0.00 sec)

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

To follow the examples exactly, you can download the sample data file here: pet.txt (save it locally and update the path in LOAD DATA LOCAL INFILE if needed).

mysql> SHOW VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXIT;

The command SET GLOBAL local_infile = 1; enables the server to accept LOAD DATA LOCAL INFILE statements. This is often disabled by default for security reasons.

• SET GLOBAL: Changes the global server setting (usually requires admin privilege). It may revert after restart unless configured in the server configuration.
• local_infile: Controls server-side acceptance of local file loading.
• = 1: Enables it (server side). Some clients also require enabling it explicitly.

Reconnect to MySQL with local infile enabled on the client side:

% mysql --local-infile=1 -u root -p

mysql> LOAD DATA LOCAL INFILE '/Users/wtong/Desktop/pet.txt' INTO TABLE pet;
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM pet;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.01 sec)

mysql> INSERT INTO pet
    ->        VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM pet;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)

mysql> DELETE FROM pet;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM pet;
Empty set (0.00 sec)

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | 0000-00-00 |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | 0000-00-00 |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | 0000-00-00 |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL       |
+----------+-------+---------+------+------------+------------+
2 rows in set (0.00 sec)


mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+


mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+


mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
       OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+


mysql> SELECT name, birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+


mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+


mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+


mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+


mysql> SELECT name, species, birth FROM pet
       ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+


/* To determine how many years old each of your pets is, use the TIMESTAMPDIFF() function. */
mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+


mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
+----------+------------+------------+------+

/* MySQL provides several functions for extracting parts of dates, such as YEAR(), MONTH(), and DAYOFMONTH(). */
mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

/* To find names beginning with b:*/

mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

/* To find names ending with fy: */
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

/* To find names containing a w: */
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

/* To find names containing exactly five characters, use five instances of the _ pattern character: */
mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

/* For more information about the syntax for regular expressions, see Regular Expressions.
   https://dev.mysql.com/doc/refman/8.0/en/regexp.html
*/

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

Please follow the official examples: Retrieving Information from a Table .

SQL statements are often grouped by what they control: structure (DDL), data (DML), permissions (DCL), and transactions (TCL).

Refer to

• w3School MySQL Tutorial
• MySQL Tutorial
• Python MySQL

• MySQL Cheat Sheet
• MySQL Cheat Sheet from learnSQL.com (PDF)

• SQL Practice
• W3Schools SQL Exercises
• W3Resource SQL Exercises + Solutions
• HackerRank: SQL